package Dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;



import entity.SalesRecord;
import entity.UserInfo;
import util.DBConnection;

public class SalesRecordDao {


    // 插入销售记录
    public static boolean saveSalesRecordAndUpdateUserAmount(SalesRecord salesRecord) throws SQLException {
        Connection conn = null;
        try {
            conn = DBConnection.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            // 保存销售记录到数据库的SQL语句
            String saveSql = "INSERT INTO SalesRecord (UserID, SpotID, TicketQuantity, ConsumptionAmount, SaleTime) VALUES (?,?,?,?,?)";
            PreparedStatement saveStatement = conn.prepareStatement(saveSql);
            saveStatement.setInt(1, salesRecord.getUserID());
            saveStatement.setInt(2, salesRecord.getSpotID());
            saveStatement.setInt(3, salesRecord.getTicketQuantity());
            saveStatement.setBigDecimal(4, salesRecord.getConsumptionAmount());
            saveStatement.setDate(5, new java.sql.Date(salesRecord.getSaleTime().getTime()));

            // 执行插入销售记录操作
            int saveResult = saveStatement.executeUpdate();

            String updateSql = "UPDATE UserInfo SET TotalConsumption = TotalConsumption +? WHERE UserID =?";
            PreparedStatement updateStatement = conn.prepareStatement(updateSql);
            updateStatement.setBigDecimal(1, salesRecord.getConsumptionAmount());
            updateStatement.setInt(2, salesRecord.getUserID());

            // 执行更新用户消费金额操作
            int updateResult = updateStatement.executeUpdate();

            // 判断插入销售记录和更新用户金额操作是否都成功，若都成功则提交事务，否则回滚事务
            if (saveResult > 0 && updateResult > 0) {
                conn.commit();

                return true;
            } else {
                conn.rollback();
                return false;
            }
        } catch (SQLException e) {
            // 出现异常回滚事务
            if (conn!= null) {
                conn.rollback();
            }
            throw e;
        } finally {
            // 关闭资源，恢复自动提交模式
            if (conn!= null) {
                conn.setAutoCommit(true);

                conn.close();
            }
        }
    }


    // 根据用户ID查询销售记录
    public static List<SalesRecord> getSalesRecordsByUserID(int userID) {
        List<SalesRecord> salesRecords = new ArrayList<>();
        String sql = "SELECT sr.*, ss.SpotName, ui.MemberLevel " +
                "FROM SalesRecord sr " +
                "JOIN ScenicSpot ss ON sr.SpotID = ss.SpotID " +
                "JOIN UserInfo ui ON sr.UserID = ui.UserID " +
                "WHERE sr.UserID = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                SalesRecord record = new SalesRecord();
                record.setRecordID(rs.getInt("RecordID"));
                record.setUserID(rs.getInt("UserID"));
                record.setSpotID(rs.getInt("SpotID"));
                record.setTicketQuantity(rs.getInt("TicketQuantity"));
                record.setConsumptionAmount(rs.getBigDecimal("ConsumptionAmount"));
                record.setSaleTime(rs.getDate("SaleTime"));
                record.setSpotName(rs.getString("SpotName")); // 设置景点名称
                record.setMemberLevel(rs.getString("MemberLevel")); // 设置会员等级

                salesRecords.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return salesRecords;
    }

    // 根据销售记录ID查询销售记录
    public SalesRecord getSalesRecordByID(int recordID) {
        String sql = "SELECT sr.*, ss.SpotName, ui.MemberLevel " +
                "FROM SalesRecord sr " +
                "JOIN ScenicSpot ss ON sr.SpotID = ss.SpotID " +
                "JOIN UserInfo ui ON sr.UserID = ui.UserID " +
                "WHERE sr.RecordID = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, recordID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                SalesRecord record = new SalesRecord();
                record.setRecordID(rs.getInt("RecordID"));
                record.setUserID(rs.getInt("UserID"));
                record.setSpotID(rs.getInt("SpotID"));
                record.setTicketQuantity(rs.getInt("TicketQuantity"));
                record.setConsumptionAmount(rs.getBigDecimal("ConsumptionAmount"));
                record.setSaleTime(rs.getDate("SaleTime"));
                record.setSpotName(rs.getString("SpotName")); // 设置景点名称
                record.setMemberLevel(rs.getString("MemberLevel")); // 设置会员等级

                return record;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 删除销售记录
    public boolean deleteSalesRecord(int recordID) {
        String sql = "DELETE FROM SalesRecord WHERE RecordID = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, recordID);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0; // 如果删除成功，返回 true
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 删除失败，返回 false
        }
    }

    // 更新销售记录
    public boolean updateSalesRecord(SalesRecord salesRecord) {
        String sql = "UPDATE SalesRecord SET UserID = ?, SpotID = ?, TicketQuantity = ?, ConsumptionAmount = ?, SaleTime = ? WHERE RecordID = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesRecord.getUserID());
            pstmt.setInt(2, salesRecord.getSpotID());
            pstmt.setInt(3, salesRecord.getTicketQuantity());
            pstmt.setBigDecimal(4, salesRecord.getConsumptionAmount());
            pstmt.setDate(5, new java.sql.Date(salesRecord.getSaleTime().getTime()));
            pstmt.setInt(6, salesRecord.getRecordID());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0; // 如果更新成功，返回 true
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 更新失败，返回 false
        }
    }

    public static  BigDecimal callInsertSalesRecordWithDiscount(int userID, int spotID, int quantity) throws SQLException {
        Connection conn =null;
        try {
            conn = DBConnection.getConnection();
            String sql="{call InsertSalesRecordWithDiscount(?,?,?,?)}";
            CallableStatement callableStatement = conn.prepareCall(sql) ;
            callableStatement.setInt(1, userID);
            callableStatement.setInt(2, spotID);
            callableStatement.setInt(3, quantity);
            try {
                callableStatement.execute();
                // 获取存储过程计算并返回的折扣后消费金额
                return callableStatement.getBigDecimal(4);
            } catch (SQLException e) {
                // 记录详细的存储过程执行异常信息

                return null;
            }
        } catch (SQLException e) {
            // 记录获取数据库连接异常信息
            return null;
        } finally {

            conn.close();
        }
    }




}
